package dblayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;




import com.microsoft.sqlserver.jdbc.SQLServerException;

import modellayer.Lead;


/**
 * The LeadDB class to exchange data betwixt SQL database and java
 * @author Allan Eriksen, Andreas Rye, Casper Madsen, Christian Stidsborg, Stefan Bols
 */

public class LeadDB {
	private Connection con;
	/**
	 * @param con
	 */
	
	
	/**
	 * Constructor for the LeadDB class, also creates a connection to the SQL Database
	 */
	public LeadDB() {
		con = DBConnection.getInstance().getDBcon();
	}
	
	/**
	 * Search method using leadId
	 * @param leadId
	 * @return singleWhere(wClause)
	 */
	public Lead findLead(int leadId) {
		String wClause = "leadId="+leadId;
		
		return singleWhere(wClause);
	}
	
	public int insertLead(Lead l) throws Exception
	{
		int rc = -1;
		
		String query = "INSERT INTO sem2_Lead (leadCompanyName, leadPersonName, leadPhone, leadMail, leadWebsite) VALUES ('"+l.getLeadCompanyName()+"', '"+l.getLeadPersonName()+"', '"+l.getLeadPhone()+"', '"+l.getLeadMail()+"', '"+l.getLeadWebsite()+"')";
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException e)
		{
			System.out.println("SQL query: " +query);
			System.out.println("error: " +e);
			
			throw new Exception("Failed to insert Lead into Database (LeadDB)");
		}
		
		return rc;
	}
	
	/**
	 * singleWhere method to execute a method in the database
	 * @param wClause
	 * @return leadObj
	 */
	private Lead singleWhere(String wClause) {
		ResultSet results;
		Lead leadObj = null;
		
		String query = buildQuery(wClause);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if ( results.next() )
			{
				leadObj = buildLeadObj(results);
			}
			else
			{
				leadObj = null;
			}
			
			stmt.close();
		}
		catch (Exception ex) {
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
		}
		
		return leadObj;
	}

	public boolean deleteLead(int LeadNo) throws Exception
	{
		boolean success = false;
		
		String query = "DELETE FROM sem2_Lead WHERE LeadId='"+LeadNo+"'";
		
		try 
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeQuery(query);	
			success = true;		
			stmt.close();
		} 
		catch (SQLServerException e) {
			success = true;
		}
		catch (Exception ex) 
		{
			throw new Exception("Lead deletetion went wrong, try again please! Error: "+ex);
		}
		
		return success;
			
	}
	
	public ArrayList<Lead> getLeads(String wClause) {
		return miscWhere(wClause);
	}
	
	private ArrayList<Lead> miscWhere(String wClause) {
		ArrayList<Lead> array = new ArrayList<Lead>();
		
		String query = buildQuery(wClause);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet results = stmt.executeQuery(query);
			
			while (results.next()) {
				array.add(buildLeadObj(results));
			}
			
		}
		catch(SQLException e) {
			System.out.println("SQL query: " +query);
			System.out.println("error: " +e);
		}
		
		return array;
	}
	
	/**
	 * A method which uses wClause parameters to construct a query, in a format that the SQL database understands
	 * @param wClause
	 * @return query
	 */
	private String buildQuery(String wClause) {
		String query = "SELECT * FROM sem2_Lead";
		
		if (wClause!=null) {
			if (!wClause.isEmpty()) {
				query += " WHERE "+wClause;
			}
		}
		
		return query;
	}

	/**
	 * A method which extracts data from the database into Java
	 * @param results
	 * @return leadObj
	 */
	private Lead buildLeadObj(ResultSet results) {
		Lead leadObj = null;
		
		try {
			leadObj = new Lead(results.getInt("leadId"), results.getString("leadCompanyName"), results.getString("leadPersonName"), results.getString("leadPhone"), results.getString("leadMail"), results.getString("leadWebsite"), results.getLong("addedDate"));
		}
		catch (SQLException e) {
			System.out.println("Error building Lead object!\nError: " + e);
		}
		
		return leadObj;
	}

	

}